﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Diagnostics;

namespace ADO_Excel
{   
    public class BinderExcel
    {   
        public class SheetExcel
        {
            public class LineExcel
            {
                private List<string> listCase;

                public LineExcel()
                {
                    listCase = new List<string>();
                }

                public LineExcel(LineExcel oLine)
                {
                    listCase = new List<string>(oLine.Count);
                    for( int i=0; i<oLine.Count; i++) 
                        listCase[i] = oLine.listCase[i];
                }

                public LineExcel(int n)
                {
                    listCase = new List<string>(n);
                }

                public LineExcel( params string[] E )
                {
                    listCase = new List<string>(E.Length);
                    for( int i=0; i<E.Length; i++) 
                        listCase[i] = E[i];
                }

                public LineExcel( List<string> E )
                {
                    listCase = new List<string>(E.Count);
                    for( int i=0; i<E.Count; i++) 
                        listCase[i] = E[i];
                }

                public string this[int n]
                {
                    get {return listCase[n];}
                    set {listCase[n] = value;}
                }

                public int this[string strName]
                {
                    get
                    {
                        for( int i=0; i<listCase.Count; i++)
                        {
                            if(listCase[i] == strName) return i;
                        }
                        return -1;
                    }
                }

                public int Count
                {
                    get
                    {
                        return listCase.Count;
                    }
                }

                public void Add(string strCase)
                {
                    listCase.Add( strCase );
                }
            }
            
            public string strName;
            private List<LineExcel> listLine;

            public SheetExcel()
            {
                listLine = new List<LineExcel>();                
            }

            public SheetExcel( SheetExcel oSheet )
            {
                strName = oSheet.strName;
                listLine = new List<LineExcel>(oSheet.Count);
                for( int i=0; i<listLine.Count; i++) 
                {
                    listLine[i] = new LineExcel( oSheet[i] );                    
                }
            }

            public SheetExcel( int n )
            {
                listLine = new List<LineExcel>(n);
                for( int i=0; i<listLine.Count; i++) 
                {
                    listLine[i] = new LineExcel();
                }
            }

            public SheetExcel( string strSheet ):this()
            {
                strName = strSheet;
            }

            public SheetExcel( string strSheet, int n ):this(n)
            {         
                strName = strSheet;
            }

            public SheetExcel( int n, int m )
            {
                listLine = new List<LineExcel>(n);
                for( int i=0; i<listLine.Count; i++) 
                {
                    listLine[i] = new LineExcel(m);
                }
            }

            public SheetExcel( params string[][] E )
            {
                listLine = new List<LineExcel>(E.Length);
                for( int i=0; i<E.Length; i++) 
                {
                    listLine[i] = new LineExcel(E[i].Length);                   
                    for( int j=0; j<E[i].Length; j++) 
                        listLine[i][j] = E[i][j];
                }
            } 

            public SheetExcel( params LineExcel[] E )
            {
                listLine = new List<LineExcel>(E.Length);
                for( int i=0; i<E.Length; i++) 
                {
                    listLine[i] = new LineExcel(E[i]);
                }

            }               
                       
            public LineExcel this[int n]
            {
                get {return listLine[n];}
                set {listLine[n] = new LineExcel(value);}
            }
            
            public int[] this[string strName]
            {
                get
                {
                    for( int i=0; i<listLine.Count; i++)
                    {
                        for( int j=0; j<listLine[i].Count; j++)
                        {
                            if(listLine[i][j] == strName) 
                                return new int[]{i,j};
                        }
                    }
                    return new int[0];
                }
            }
            
            public int Count
            {
                get
                {
                    return listLine.Count;
                }
            }

            public void Add(LineExcel oLine)
            {
                listLine.Add( oLine );
            }

            public void Add(int nLine, string strCase)
            {
                if( nLine>=0 && nLine<listLine.Count )
                {
                    listLine[nLine].Add( strCase );
                }
            }
        }
        
        private List<SheetExcel> listSheet;        

        public BinderExcel(string[][][] E)
        {
            listSheet = new List<SheetExcel>(E.Length);
            for( int i=0; i<E.Length; i++) 
            {
                listSheet[i] = new SheetExcel(E[i].Length);  
                for( int j=0; j<E[i].Length; j++) 
                {
                    listSheet[i][j] = new SheetExcel.LineExcel(E[i][j].Length);  
                    for( int k=0; k<E[i][j].Length; k++) 
                    {
                        listSheet[i][j][k] = E[i][j][k];
                    }
                }
            }
        }

        public BinderExcel(params SheetExcel[] E)
        {
            listSheet = new List<SheetExcel>(E.Length);
            for( int i=0; i<E.Length; i++) listSheet[i] = new SheetExcel(E[i]);
        }

        public SheetExcel this[int n]
        {
            get {return listSheet[n];}
            set {listSheet[n] = value;}
        }

        public int this[string strName]
        {
            get
            {
                for( int i=0; i<listSheet.Count; i++)
                {
                    if(listSheet[i].strName == strName) return i;
                }
                return -1;
            }
        }

        public int Length
        {
            get
            {
                return listSheet.Count;
            }
        }

        public void Add(SheetExcel oSheet)
        {
            listSheet.Add( oSheet );
        }

        public void Add(int nSheet, int nLine, string strCase)
        {
            if( nSheet>=0 && nSheet<listSheet.Count )
            {
                listSheet[nSheet].Add( nLine, strCase );
            }
        }

        public void Add(string strSheet, int nLine, string strCase)
        {
            int nIndexSheet = this[strSheet];
            if( nIndexSheet == -1 )
                return;
            SheetExcel oSheet = listSheet[nIndexSheet];
            if( oSheet != null )
            {
                oSheet.Add( nLine, strCase );
            }
        }

        public  string strFilePath;
        public  string strConnectionString;
        private OleDbConnection oConnection;
        
        public BinderExcel()
        {
            listSheet    = new List<SheetExcel>();            
            /*
            strFilePath = Environment.CurrentDirectory+"\\Army.xlsx";
            strConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source =" + strFilePath;
            strConnectionString += @"; Extended Properties =""Excel 12.0;HDR=No;IMEX=1"";";
             */
        }
        
        public BinderExcel( string strFilePath, bool bWithoutFirstLine=false ):this()
        {            
            this.strFilePath            = strFilePath;                               

            string strHDR="Yes"; // 1er ligne importée (elle contient ds données)
            if( bWithoutFirstLine )
                strHDR = "No"; // 1er ligne non importée (contient les entêtes de colonne)

            strConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source =" + strFilePath;
            strConnectionString += @"; Extended Properties =""Excel 12.0;HDR="+strHDR+@";IMEX=1"";";
        } 
        
        public BinderExcel( string strFilePath, string strConnectionString ):this()
        {            
            this.strFilePath            = strFilePath;
            this.strConnectionString    = strConnectionString;            
        }

        public void Read()
        {
            BuildListFeuille();
        }

        private void BuildListFeuille()
        {
            try
            {

	            using (oConnection = new OleDbConnection(strConnectionString))
	            {
                    // Ouverture de la connexion
		            oConnection.Open();

                    // Récupération de la structure du fichier Ecel
		            var dataTable = oConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
		            if (dataTable == null)
			            return;

		           // Récupération des Feuilles du fichier Excel
                    string[] excelSheets = new String[dataTable.Rows.Count];
                    int i = 0;
                    foreach(DataRow row in dataTable.Rows)
                    {
                        excelSheets[i] = row["TABLE_NAME"].ToString();
                        i++;
                    }
                    if( excelSheets.Length <= 0)
                        return;
                    
                    // Initialisation du nombre de Feuille dans le classeur
                    listSheet = new List<SheetExcel>();

                    // Parcours des feuilles du fichier Excel                    
                    SheetExcel oSheet;
                    SheetExcel.LineExcel oLine;
                    foreach( string strSheetName in excelSheets )
                    {
                        // Execution de la commande récuppérant toutes les infos de la feuille Excel
		                string cmdText          = "SELECT * FROM [" + strSheetName + "]";
		                OleDbCommand command    = new OleDbCommand(cmdText, oConnection);
		                OleDbDataReader reader  = command.ExecuteReader();
		                if (reader == null || !reader.HasRows) continue;
	                    /*
                        cmdText ="SELECT COUNT(*) FROM [" + strSheetName + "]";
                        OleDbCommand commande = new OleDbCommand(cmdText, oConnection);                        
                        int nNbLigne= (int)commande.ExecuteScalar();
                        */
                        
                        oSheet = new SheetExcel(strSheetName);   

                        // Parcours des lignes de la feuille Excel                        
                        while (reader.Read())
		                {
                            oLine = new SheetExcel.LineExcel();                            

                            // Parcours des colonnes de la feuille Excel
                            bool bIsEmptyLine = true; // Indique si la ligne est totalement vide
			                for(int c=0; c<reader.FieldCount; c++)
                            {                            
                                string strCase = reader[c].ToString();

                                // Enregistrement de l'info de la case sur la ligne
                                oLine.Add(strCase);
                                if( strCase != "" )
                                    bIsEmptyLine = false;
                            }
                            
                            // Enregistrement de l'info de la ligne sur la feuille
                            if( oLine.Count > 0 && !bIsEmptyLine)
                            {
			                    oSheet.Add(oLine);                                
                            }
                            
		                }
                        // Enregistrement de l'info de l'ensemble de la feuille sur le classeur
                        if( oSheet.Count > 0 )
                        {
                            listSheet.Add(oSheet);                            
                        }
                        
                    }
	            }
            }            
            catch (Exception e)
            {
	            Debug.WriteLine("***** Exception : "+e.ToString()) ;
            }
            finally
            {
	            if (oConnection != null) oConnection.Close();                
            }
        }
    }
}
